package com.lyq.service;

import com.alibaba.fastjson.JSONObject;
import com.lyq.util.PoiPictureUtils;
import com.lyq.vo.CellModel;
import com.lyq.vo.TestGoodsInVo;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @className: DemoService
 * @description: demo
 * @author: XTJ
 * @date: 2023/05/26 14:22
 **/
@Service
public class DemoService {

    @Autowired
    private TransactionService transactionService;

    public void importDemo(MultipartFile multipartFile) {
        try {
            Workbook workbook = WorkbookFactory.create(multipartFile.getInputStream());
            //获取第一个工作表（多个工作表就套循环呗）
            Sheet sheet = workbook.getSheetAt(0);
            // 获取图片
            Map<String, List<PictureData>> maplist = null;
            //获取所有图片！
            if (multipartFile.getOriginalFilename().endsWith("xls")) {
                maplist = PoiPictureUtils.getPictures3((HSSFSheet) sheet, (HSSFWorkbook) workbook);
            } else {
                return AjaxResult.error("文件格式不正确,只支持xls");
            }

            //将图片上传服务器或者oss
            Map<String, List<String>> printImgList = printImg(maplist);
            //获取sheet中最后一行行号
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                transactionService.begin();
                addDemo(row, i, printImgList);
                //手动提交事务
                transactionService.commit();
            }
        } catch (Exception e) {
            //log.error(e.getMessage());
            //手动回滚事务
            //最好是放在catch 里面,防止程序异常而事务一直卡在哪里未提交
            transactionService.rollback();
            //记得new全局异常，否则报错后，就停止执行了
            //new 全局异常(e.getMessage());
        }
        //return AjaxResult.success();
    }

    private void addDemo(Row row, Integer i, Map<String, List<String>> printImgList) {
        //拼接坐标  1_16 为什么这么拼？  因为UTil存的key是这样的！
        String goodsName = getCellValue(row, 0);
        String goodsPic1 = i + "_1";

        //在这拿图片
        if (printImgList.get(goodsPic1) != null) {
            List<String> list = printImgList.get(goodsPic1);
            //都到这了?你还看不懂？
            for (String picUrl : list) {

            }
        }
    }


    private String getCellValue(Row row, int idx) {
        return row.getCell(idx) == null ? "" : new DataFormatter().formatCellValue(row.getCell(idx));
    }

    //写入图片，并返回图片路径，key：图片坐标，value：图片路径
    private static Map<String, List<String>> printImg(Map<String, List<PictureData>> sheetList) throws IOException {

        Map<String, List<String>> pathMap = new HashMap();
        Object[] key = sheetList.keySet().toArray();
        List<String> list = null;
        for (int i = 0; i < sheetList.size(); i++) {
            list = new ArrayList<>();
            // 获取图片索引
            String picName = key[i].toString();
            // 获取图片流
            List<PictureData> pics = sheetList.get(key[i]);
            if (null != pics && pics.size() > 0) {
                for (PictureData pic : pics) {
                    //图片流
                    pic.getData();
                    //图片格式  后缀
                    pic.suggestFileExtension();
                    // 获取图片格式  后缀
                    String ext = pic.suggestFileExtension();

                    //此处可以处理将图片转存到服务器
                    /*String fileName = ToolFun.createOrderNum(null, 10) + "." + ext;
                    String path = System.getProperty("user.dir") + "\date\img" + fileName;
                    byte[] data = pic.getData();

                    ByteArrayInputStream bis = new ByteArrayInputStream(data);
                    BufferedImage bImage2 = ImageIO.read(bis);
                    ImageIO.write(bImage2, ext, new File(path));
                    list.add(UploadFileUtil.uploadFile(path));*/

                    //地址存入
                    String url = null;
                    list.add(url);
                }
            }
            pathMap.put(picName, list);
        }
        return pathMap;
    }

    public void exportTestGoodsInVo(TestGoodsInVo testGoodsInVo, HttpServletResponse response) {

        //1.获取到数据
        List<TestGoodsInVo> testGoodsInVos = new ArrayList<>();

        // 组装Map数据
        List<Map<String, Object>> mapList = splicingExport(testGoodsInVos);

        //2.设置表头
        Map<String, List<CellModel>> cellTitleMap = getTitleListMap("第一行表头");

        //3.生成表格，填充数据
        SXSSFWorkbook workbook = createWorkbook("商品信息", new SXSSFWorkbook(),cellTitleMap , 3, mapList);

        //4.浏览器导出
        String fileName = "导出名.xls";
        OutputStream os = null;// 取得输出流
        try {
            os = response.getOutputStream();// 取得输出流
            response.reset();

            // 下面几行是为了解决文件名乱码的问题
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-disposition","attachment;filename="+new String(fileName.getBytes(), StandardCharsets.UTF_8));
            response.setHeader("wms-filename", fileName);

            response.flushBuffer();
            workbook.write(os);
            os.flush();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
                os.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 组装导出数据 (第一步，导出数据组装)
     */
    public List<Map<String, Object>> splicingExport(List<TestGoodsInVo> testGoodsInVos) {
        List<Map<String, Object>> dataList = new ArrayList<>();

        if (CollectionUtils.isNotEmpty(testGoodsInVos)) {
            Integer i = 1;
            for (TestGoodsInVo testGoodsInVo : testGoodsInVos) {
                Map<String, Object> baseItemMap = new HashMap<>(16);
                baseItemMap.put("NO", i++);
                baseItemMap.put("goodsName", testGoodsInVo.getGoodsName());
                baseItemMap.put("goodsDescription", testGoodsInVo.getGoodsDescription());
                baseItemMap.put("goodsMinPrice", testGoodsInVo.getGoodsMinPrice());
                baseItemMap.put("goodsMaxPrice", testGoodsInVo.getGoodsMaxPrice());

                // 属性List
                List<Map<String, Object>> attributeList = new ArrayList<>();
                if (CollectionUtils.isNotEmpty(testGoodsInVo.getGoodsAttributeList())) {

                    for (int i1 = 0; i1 < testGoodsInVo.getGoodsAttributeList().size(); i1++) {
                        Map<String, Object> attributeMap = new HashMap<>(16);
                        String goodsAttribute = String.valueOf(testGoodsInVo.getGoodsAttributeList().get(i1));
                        TestGoodsInVo.GoodsAttributeJson parseObject = JSONObject.parseObject(goodsAttribute, TestGoodsInVo.GoodsAttributeJson.class);
                        attributeMap.put("attributeName", parseObject.getAttributeName() + " ");
                        attributeMap.put("attributeVal", parseObject.getAttributeVal() + " ");
                        attributeList.add(attributeMap);
                    }
                } else {
                    Map<String, Object> attributeMap = new HashMap<>(16);
                    attributeMap.put("attributeName", "");
                    attributeMap.put("attributeVal", "");
                    attributeList.add(attributeMap);
                }
                baseItemMap.put("attributeList", attributeList);

                // 规格List
                List<Map<String, Object>> specList = new ArrayList<>();
                if (CollectionUtils.isNotEmpty(testGoodsInVo.getGoodsSpecList())) {
                    for (int i1 = 0; i1 < testGoodsInVo.getGoodsSpecList().size(); i1++) {
                        Map<String, Object> specMap = new HashMap<>(16);
                        String goodsSpec = String.valueOf(testGoodsInVo.getGoodsSpecList().get(i1));
                        TestGoodsInVo.GoodsSpecJson parseObject = JSONObject.parseObject(goodsSpec, TestGoodsInVo.GoodsSpecJson.class);
                        specMap.put("specName", parseObject.getSpecName() + " ");
                        specMap.put("specType", parseObject.getSpecType() + " ");
                        specList.add(specMap);
                    }
                } else {
                    Map<String, Object> specMap = new HashMap<>(16);
                    specMap.put("specName", "");
                    specMap.put("specType", "");
                    specList.add(specMap);
                }
                baseItemMap.put("specList", specList);

                // SkuList
                List<Map<String, Object>> skuList = new ArrayList<>();
                if (CollectionUtils.isNotEmpty(testGoodsInVo.getGoodsSkuList())) {
                    for (int i1 = 0; i1 < testGoodsInVo.getGoodsSkuList().size(); i1++) {
                        Map<String, Object> skuMap = new HashMap<>(16);
                        String goodsSpec = String.valueOf(testGoodsInVo.getGoodsSkuList().get(i1));
                        TestGoodsInVo.GoodsSkuJson parseObject = JSONObject.parseObject(goodsSpec, TestGoodsInVo.GoodsSkuJson.class);
                        skuMap.put("specCode", parseObject.getSpecCode() + " ");
                        skuMap.put("skuPrice", parseObject.getSkuPrice() + " ");
                        skuMap.put("skuInventory", parseObject.getSkuInventory() + " ");
                        skuList.add(skuMap);
                    }
                } else {
                    Map<String, Object> skuMap = new HashMap<>(16);
                    skuMap.put("specCode", "");
                    skuMap.put("skuPrice", "");
                    skuMap.put("skuInventory", "");
                    skuList.add(skuMap);
                }
                baseItemMap.put("skuList", skuList);

                dataList.add(baseItemMap);
            }
        }
        return dataList;
    }

    /**
     * 组装EXCEl表头，一共三行数据
     * @param titleStr 提头
     * @return
     */
    private Map<String, List<CellModel>> getTitleListMap(String titleStr) {
        Map<String, List<CellModel>> cellTitleMap = new HashMap<>(16);
        // 提头
        List<CellModel> titleRow = new ArrayList<>();
        CellModel title = new CellModel();
        title.setCellName(titleStr);
        title.setStartRow(0);
        title.setEndRow(0);
        title.setStartColumn(0);
        title.setEndColumn(11);//表头总长度
        titleRow.add(title);

        // 创建第一行
        List<CellModel> firstRow = new ArrayList<>();

        CellModel row1PnCode = new CellModel();
        row1PnCode.setCellName("NO");
        row1PnCode.setStartRow(1);
        row1PnCode.setWidth(20);
        row1PnCode.setEndRow(2);
        row1PnCode.setStartColumn(0);
        row1PnCode.setEndColumn(0);
        firstRow.add(row1PnCode);

        CellModel row1GoodsName = new CellModel();
        row1GoodsName.setCellName("商品名称");
        row1GoodsName.setStartRow(1);
        row1GoodsName.setWidth(20);
        row1GoodsName.setEndRow(2);
        row1GoodsName.setStartColumn(1);
        row1GoodsName.setEndColumn(1);
        firstRow.add(row1GoodsName);

        CellModel row1GoodsDescription = new CellModel();
        row1GoodsDescription.setCellName("商品描述");
        row1GoodsDescription.setStartRow(1);
        row1GoodsDescription.setWidth(20);
        row1GoodsDescription.setEndRow(2);
        row1GoodsDescription.setStartColumn(2);
        row1GoodsDescription.setEndColumn(2);
        firstRow.add(row1GoodsDescription);

        CellModel row1GoodsMinPrice = new CellModel();
        row1GoodsMinPrice.setCellName("商品最低价");
        row1GoodsMinPrice.setStartRow(1);
        row1GoodsMinPrice.setEndRow(2);
        row1GoodsMinPrice.setStartColumn(3);
        row1GoodsMinPrice.setEndColumn(3);
        firstRow.add(row1GoodsMinPrice);

        CellModel row1GoodsMaxPrice = new CellModel();
        row1GoodsMaxPrice.setCellName("商品最高价");
        row1GoodsMaxPrice.setStartRow(1);
        row1GoodsMaxPrice.setEndRow(2);
        row1GoodsMaxPrice.setStartColumn(4);
        row1GoodsMaxPrice.setEndColumn(4);
        firstRow.add(row1GoodsMaxPrice);

        CellModel rowDetail = new CellModel();
        rowDetail.setCellName("商品属性");
        rowDetail.setStartRow(1);
        rowDetail.setWidth(20);
        rowDetail.setEndRow(1);
        rowDetail.setStartColumn(5);
        rowDetail.setEndColumn(6);
        firstRow.add(rowDetail);

        CellModel row1Spec = new CellModel();
        row1Spec.setCellName("规格属性");
        row1Spec.setStartRow(1);
        row1Spec.setWidth(20);
        row1Spec.setEndRow(1);
        row1Spec.setStartColumn(7);
        row1Spec.setEndColumn(8);
        firstRow.add(row1Spec);

        CellModel row1Sku = new CellModel();
        row1Sku.setCellName("规格Sku属性");
        row1Sku.setStartRow(1);
        row1Sku.setWidth(20);
        row1Sku.setEndRow(1);
        row1Sku.setStartColumn(9);
        row1Sku.setEndColumn(11);
        firstRow.add(row1Sku);

        // 第二行
        List<CellModel> secondRow = new ArrayList<>();


        CellModel row2AttributeName = new CellModel();
        row2AttributeName.setCellName("属性名称");
        row2AttributeName.setStartRow(2);
        row2AttributeName.setEndRow(2);
        row2AttributeName.setWidth(20);
        row2AttributeName.setStartColumn(5);
        row2AttributeName.setEndColumn(5);
        secondRow.add(row2AttributeName);

        CellModel row2AttributeVal = new CellModel();
        row2AttributeVal.setCellName("属性值");
        row2AttributeVal.setStartRow(2);
        row2AttributeVal.setEndRow(2);
        row2AttributeVal.setWidth(20);
        row2AttributeVal.setStartColumn(6);
        row2AttributeVal.setEndColumn(6);
        secondRow.add(row2AttributeVal);


        CellModel row2SpecName = new CellModel();
        row2SpecName.setCellName("规格名称");
        row2SpecName.setStartRow(2);
        row2SpecName.setEndRow(2);
        row2SpecName.setWidth(20);
        row2SpecName.setStartColumn(7);
        row2SpecName.setEndColumn(7);
        secondRow.add(row2SpecName);

        CellModel row2SpecType = new CellModel();
        row2SpecType.setCellName("规格值数组");
        row2SpecType.setStartRow(2);
        row2SpecType.setEndRow(2);
        row2SpecType.setWidth(20);
        row2SpecType.setStartColumn(8);
        row2SpecType.setEndColumn(8);
        secondRow.add(row2SpecType);


        CellModel row2SpecCode = new CellModel();
        row2SpecCode.setCellName("规格组合");
        row2SpecCode.setStartRow(2);
        row2SpecCode.setEndRow(2);
        row2SpecCode.setWidth(20);
        row2SpecCode.setStartColumn(9);
        row2SpecCode.setEndColumn(9);
        secondRow.add(row2SpecCode);

        CellModel row2SkuPrice = new CellModel();
        row2SkuPrice.setCellName("sku售价");
        row2SkuPrice.setStartRow(2);
        row2SkuPrice.setEndRow(2);
        row2SkuPrice.setWidth(20);
        row2SkuPrice.setStartColumn(10);
        row2SkuPrice.setEndColumn(10);
        secondRow.add(row2SkuPrice);

        CellModel row2SkuInventory = new CellModel();
        row2SkuInventory.setCellName("sku库存");
        row2SkuInventory.setStartRow(2);
        row2SkuInventory.setEndRow(2);
        row2SkuInventory.setWidth(20);
        row2SkuInventory.setStartColumn(11);
        row2SkuInventory.setEndColumn(11);
        secondRow.add(row2SkuInventory);

        // 组装提头
        cellTitleMap.put("0", titleRow);
        // 组装第二行表头标题
        cellTitleMap.put("1", firstRow);
        // 组装第二行表头标题
        cellTitleMap.put("2", secondRow);
        return cellTitleMap;
    }

    /**
     * 生成表格（用于生成复杂表头）
     *
     * @param sheetName    sheet名称
     * @param wb           表对象
     * @param cellTitleMap 表头数据
     * @param cellRowNum   表头总占用行数
     * @param exportData   行数据
     * @return SXSSFWorkbook 数据表对象
     */
    @SuppressWarnings({"rawtypes", "unchecked"})
    public static SXSSFWorkbook createWorkbook(String sheetName, SXSSFWorkbook wb,
                                               Map<String, List<CellModel>> cellTitleMap,
                                               Integer cellRowNum, List<Map<String, Object>> exportData) {
        // 设置表格名称
        Sheet sheet = wb.createSheet(sheetName);
        // 定义title列cell样式
        CellStyle cellTitleStyle = wb.createCellStyle();
        cellTitleStyle.setAlignment(HorizontalAlignment.CENTER); // 文字居中
        cellTitleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 设置单元格内容垂直对齐
        cellTitleStyle.setWrapText(true); // 设置自动换行
        cellTitleStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex()); // 背景色
        cellTitleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 必须设置 否则背景色不生效
        cellTitleStyle.setBorderBottom(BorderStyle.THIN);
        cellTitleStyle.setBorderLeft(BorderStyle.THIN);
        cellTitleStyle.setBorderRight(BorderStyle.THIN);
        cellTitleStyle.setBorderTop(BorderStyle.THIN);
        // 定义title列cell字体
        Font titleFont = wb.createFont();
        titleFont.setFontName("Arial");
        titleFont.setColor(IndexedColors.WHITE.getIndex()); //字体颜色
        titleFont.setFontHeightInPoints((short) 10);
        titleFont.setBold(true);
        cellTitleStyle.setFont(titleFont);
        List<List<Integer>> mergeParams = new ArrayList<>(); // 需要合并的数据
        for (int t = 0; t < cellRowNum; t++) {
            Row row = sheet.createRow(t);
            if (t == 0) {
                row.setHeight((short) (2 * 256));
            }
            List<CellModel> cellNameList = cellTitleMap.get(String.valueOf(t));
            for (CellModel cellModel : cellNameList) {
                // 遍历插入表头
                if (cellModel.getStartColumn() != null) {
                    Cell cell = row.createCell(cellModel.getStartColumn());
                    cell.setCellValue(cellModel.getCellName());
                    cell.setCellStyle(cellTitleStyle);
                }
                Integer startRow = cellModel.getStartRow();
                Integer endRow = cellModel.getEndRow();
                Integer startColumn = cellModel.getStartColumn();
                Integer endColumn = cellModel.getEndColumn();
                // 满足合并单元格条件，加入到合并集合
                if (!startRow.equals(endRow) || !startColumn.equals(endColumn)) {
                    List<Integer> mergeParam = new ArrayList<>(4);
                    mergeParam.add(startRow);
                    mergeParam.add(endRow);
                    mergeParam.add(startColumn);
                    mergeParam.add(endColumn);
                    mergeParams.add(mergeParam);
                }
                // 根据标题设置单元格宽度
                if (cellModel.getWidth() != null) {
                    sheet.setColumnWidth(startColumn, cellModel.getWidth() * 256);
                } else {
                    sheet.setColumnWidth(startColumn, cellModel.getCellName().getBytes().length * 256);
                }
            }
        }
        // 合并单元格
        if (StringUtils.isNotEmpty(mergeParams)) {
            for (List<Integer> list : mergeParams) {
                // 合并单元格之前设置单元格的样式，避免合并后部分失效
                Integer startRow = list.get(0);
                Integer endRow = list.get(1);
                Integer startCell = list.get(2);
                Integer endCell = list.get(3);
                setStyleBeforeMerging(sheet, startRow, endRow, startCell, endCell, cellTitleStyle);
                sheet.addMergedRegionUnsafe(new CellRangeAddress(startRow, endRow, startCell, endCell));
            }
        }

        // 明细数据样式
        CellStyle bodyStyle = wb.createCellStyle();
        bodyStyle.setAlignment(HorizontalAlignment.CENTER);   // 设置单元格内容水平对齐
        bodyStyle.setVerticalAlignment(VerticalAlignment.CENTER);  // 设置单元格内容垂直对齐
        bodyStyle.setWrapText(true);  // 设置自动换行
        bodyStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        bodyStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 必须设置 否则背景色不生效
        bodyStyle.setBorderBottom(BorderStyle.THIN);
        bodyStyle.setBorderLeft(BorderStyle.THIN);
        bodyStyle.setBorderRight(BorderStyle.THIN);
        bodyStyle.setBorderTop(BorderStyle.THIN);
        // 明细数据样式字体样式
        Font bodyFont = wb.createFont();
        bodyFont.setFontName("Arial");
        bodyFont.setFontHeightInPoints((short) 10);
        bodyStyle.setFont(bodyFont);

        // 填充数据
        fillExcelData(exportData, sheet, bodyStyle);

        return wb;
    }

    /**
     * 填充数据
     *
     * @param mapsList
     */
    public static void fillExcelData(List<Map<String, Object>> mapsList, Sheet sheet, CellStyle cellStyle) {
        int rowIndex = 3;
        String[] baseInfoStrArr = {"NO", "goodsName", "goodsDescription", "goodsMinPrice", "goodsMaxPrice"};
        String[] attributeStrArr = {"attributeName", "attributeVal"};
        String[] goodsSpecStrArr = {"specName", "specType"};
        String[] goodsSkuStrArr = {"specCode", "skuPrice", "skuInventory"};

        List<List<Integer>> mergeParams = new ArrayList<>(); // 需要合并的数据
        for (Map<String, Object> baseMap : mapsList) {
            List<Map<String,Object>> attributeJson = (List<Map<String, Object>>)  baseMap.get("attributeList");
            List<Map<String,Object>> goodsSpecJson = (List<Map<String, Object>>)  baseMap.get("specList");
            List<Map<String,Object>> goodsSkuJson = (List<Map<String, Object>>)  baseMap.get("skuList");

            Integer detailSize = 0;
            if (detailSize < attributeJson.size()){
                detailSize = attributeJson.size();
            }

            if (detailSize < goodsSpecJson.size()){
                detailSize = goodsSpecJson.size();
            }

            if (detailSize < goodsSkuJson.size()){
                detailSize = goodsSkuJson.size();
            }

            int startIndex = rowIndex;
            // 填充明细数据
            for (int i = 0; i < detailSize; i++) {
                // 建立明细行
                Row detailRow = sheet.createRow(rowIndex);
                int cellIndex = 0;
                // 基础数据
                for (String value : baseInfoStrArr) {
                    Cell cell = detailRow.createCell(cellIndex);
                    setExcelValue(cell, baseMap.get(value), cellStyle);
                    cellIndex++;
                }

                // 属性数据
                if (attributeJson.size()>i && attributeJson.get(i) != null){
                    for (String value : attributeStrArr) {
                        Cell cell = detailRow.createCell(cellIndex);
                        setExcelValue(cell, attributeJson.get(i).get(value), cellStyle);
                        cellIndex++;
                    }
                }else {
                    for (String value : attributeStrArr) {
                        Cell cell = detailRow.createCell(cellIndex);
                        setExcelValue(cell, null, cellStyle);
                        cellIndex++;
                    }
                }

                // 规格数据
                if (goodsSpecJson.size()>i && goodsSpecJson.get(i) != null){
                    for (String value : goodsSpecStrArr) {
                        Cell cell = detailRow.createCell(cellIndex);
                        setExcelValue(cell, goodsSpecJson.get(i).get(value), cellStyle);
                        cellIndex++;
                    }
                }else {
                    for (String value : goodsSpecStrArr) {
                        Cell cell = detailRow.createCell(cellIndex);
                        setExcelValue(cell, null, cellStyle);
                        cellIndex++;
                    }
                }

                // sku数据
                if (goodsSkuJson.size()>i && goodsSkuJson.get(i) != null){
                    for (String value : goodsSkuStrArr) {
                        Cell cell = detailRow.createCell(cellIndex);
                        setExcelValue(cell, goodsSkuJson.get(i).get(value), cellStyle);
                        cellIndex++;
                    }
                }else {
                    for (String value : goodsSkuStrArr) {
                        Cell cell = detailRow.createCell(cellIndex);
                        setExcelValue(cell, null, cellStyle);
                        cellIndex++;
                    }
                }

                rowIndex++;
            }
            if (detailSize > 1) { // 需要合并单元格加入集合
                int baseLength = baseInfoStrArr.length;
                int bi = 0;
                for (; bi < baseLength; bi++) {
                    List<Integer> mergeParam = new ArrayList<>(4);
                    mergeParam.add(startIndex);
                    mergeParam.add(startIndex + detailSize - 1);
                    mergeParam.add(bi);
                    mergeParam.add(bi);
                    mergeParams.add(mergeParam);
                }
            }
        }
        if (StringUtils.isNotEmpty(mergeParams)) {
            // 合并单元格
            for (List<Integer> list : mergeParams) {
                sheet.addMergedRegionUnsafe(new CellRangeAddress(list.get(0), list.get(1), list.get(2), list.get(3)));
            }
        }
    }


    /**
     * <p>
     * Description: 合并单元格之前设置单元格的样式，避免合并后部分失效
     * </p>
     *
     * @param sheet    excel的sheet
     * @param firstRow 开始行下标
     * @param lastRow  结束行下标
     * @param firstCol 开始列下标
     * @param lastCol  结束列下标
     * @param style    样式
     */
    public static void setStyleBeforeMerging(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, CellStyle style) {
        if (lastRow >= firstRow) {
            //设置合并单元格之前，先设置一遍样式
            for (int l = firstRow; l <= lastRow; l++) {
                Row row = CellUtil.getRow(l, sheet);
                for (int k = firstCol; k <= lastCol; k++) {
                    Cell cell = CellUtil.getCell(row, k);
                    cell.setCellStyle(style);
                }
            }
        }
    }

    /**
     * 设置单元格值
     *
     * @param cell  单元格类
     * @param value 传入的值
     */
    public static void setExcelValue(Cell cell, Object value, CellStyle cellStyle) {
        // 写数据
        if (value == null) {
            cell.setCellValue("");
        } else {
            if (value instanceof Integer || value instanceof Long) {
                cell.setCellValue(Long.parseLong(value.toString()));
            } else if (value instanceof BigDecimal) {
                cell.setCellValue(((BigDecimal) value).setScale(1, RoundingMode.HALF_UP).doubleValue());
            } else {
                cell.setCellValue(value.toString());
            }
        }
        cell.setCellStyle(cellStyle);
    }
}
